﻿using System;
using System.Collections.Generic;
using System.Web;
using System.Data;
using System.Data.SqlClient;

/// <summary>
/// Summary description for TicketDAL
/// </summary>
public class TicketDAL
{
    DABasic _dabasic;
	public TicketDAL()
	{
        _dabasic = new DABasic();
	}

    public int NewTicket(string userid,int flightid,string status,string firstname,string lastname,string age,bool isMale,string seatClass,int price )
    {
        string _command = "_Ticket_Insert";

        SqlParameter[] _para = new SqlParameter[10];

        _para[0] = new SqlParameter("@userid", SqlDbType.UniqueIdentifier);
        _para[0].Value = new Guid(userid);

        _para[1] = new SqlParameter("@flightid", SqlDbType.Int);
        _para[1].Value = flightid;

        _para[2] = new SqlParameter("@status", SqlDbType.VarChar,20);
        _para[2].Value = status;

        _para[3] = new SqlParameter("@firstname", SqlDbType.VarChar,50);
        _para[3].Value = firstname;

        _para[4] = new SqlParameter("@lastname", SqlDbType.VarChar,50);
        _para[4].Value = lastname;

        _para[5] = new SqlParameter("@age", SqlDbType.Int);
        _para[5].Value = age;

        _para[6] = new SqlParameter("@isMale", SqlDbType.Bit);
        _para[6].Value = isMale;

        _para[7] = new SqlParameter("@seatClass", SqlDbType.VarChar,20);
        _para[7].Value = seatClass;

        _para[8] = new SqlParameter("@price", SqlDbType.Int);
        _para[8].Value = price;

        _para[9] = new SqlParameter("@ticketID", SqlDbType.Int);
        _para[9].Direction = ParameterDirection.Output;

        SqlParameterCollection output = _dabasic.ExecuteReturnParameters(_command, _para, CommandType.StoredProcedure);
        return Convert.ToInt32(output["@ticketID"].Value);
    }

    public int FillTicketDetail(int ticketID,string firstname, string lastname, string age, bool isMale, string seatClass, int price)
    {
        string _command = "_Ticket_InsertDetail";

        SqlParameter[] _para = new SqlParameter[7];
        _para[0] = new SqlParameter("@firstname", SqlDbType.VarChar, 50);
        _para[0].Value = firstname;

        _para[1] = new SqlParameter("@lastname", SqlDbType.VarChar, 50);
        _para[1].Value = lastname;

        _para[2] = new SqlParameter("@age", SqlDbType.Int);
        _para[2].Value = age;

        _para[3] = new SqlParameter("@isMale", SqlDbType.Bit);
        _para[3].Value = isMale;

        _para[4] = new SqlParameter("@seatClass", SqlDbType.VarChar, 20);
        _para[4].Value = seatClass;

        _para[5] = new SqlParameter("@price", SqlDbType.Int);
        _para[5].Value = price;

        _para[6] = new SqlParameter("@ticketID", SqlDbType.Int);
        _para[6].Value = ticketID;
        _para[6].Direction = ParameterDirection.InputOutput;

        SqlParameterCollection output = _dabasic.ExecuteReturnParameters(_command, _para, CommandType.StoredProcedure);
        return Convert.ToInt32(output["@ticketID"].Value);
    }

    public int getDiscount(DateTime departtime, string type)
    {
        string _command = "_Ticket_GetDiscount";

        SqlParameter[] _para = new SqlParameter[3];
        _para[0] = new SqlParameter("@departtime", SqlDbType.DateTime);
        _para[0].Value = departtime;

        _para[1] = new SqlParameter("@type", SqlDbType.VarChar, 20);
        _para[1].Value = type;

        _para[2] = new SqlParameter("@discount", SqlDbType.Int);
        _para[2].Direction = ParameterDirection.Output;

        SqlParameterCollection output = _dabasic.ExecuteReturnParameters(_command, _para, CommandType.StoredProcedure);
        return Convert.ToInt32(output["@discount"].Value);
    }

    public bool checkTicket(int ticketid, string userid,string status)
    {
        string _command = "_Ticket_CheckExists";

        SqlParameter[] _para = new SqlParameter[4];
        _para[0] = new SqlParameter("@ticketid", SqlDbType.Int);
        _para[0].Value = ticketid;

        _para[1] = new SqlParameter("@userid", SqlDbType.UniqueIdentifier);
        _para[1].Value = new Guid(userid);

        _para[2] = new SqlParameter("@exists", SqlDbType.Int);
        _para[2].Direction = ParameterDirection.Output;

        _para[3] = new SqlParameter("@status", SqlDbType.VarChar,20);
        _para[3].Value = status;

        SqlParameterCollection output = _dabasic.ExecuteReturnParameters(_command, _para, CommandType.StoredProcedure);
        return Convert.ToBoolean(Convert.ToInt32(output["@exists"].Value));
    }

    public int CancelTicket(int ticketid)
    {
        string _command = "_Ticket_Cancel";

        SqlParameter[] _para = new SqlParameter[2];
        _para[0] = new SqlParameter("@ticketID", SqlDbType.Int);
        _para[0].Value = ticketid;

        _para[1] = new SqlParameter("@price", SqlDbType.Int);
        _para[1].Direction = ParameterDirection.Output;

        SqlParameterCollection output = _dabasic.ExecuteReturnParameters(_command, _para, CommandType.StoredProcedure);
        return Convert.ToInt32(output["@price"].Value);
    }

    internal void confirmTicket(int ticketID)
    {
        string _comman = "_Ticket_Confirm";

        SqlParameter[] _para = new SqlParameter[1];

        _para[0] = new SqlParameter("@ticketID", SqlDbType.Int);
        _para[0].Value = ticketID;

        _dabasic.ExecuteReturnParameters(_comman, _para, CommandType.StoredProcedure);
    }

    internal void confirmDetail(int DetailID, int price)
    {
        string _comman = "_Ticket_ConfirmDetail";

        SqlParameter[] _para = new SqlParameter[2];

        _para[0] = new SqlParameter("@DetailID", SqlDbType.Int);
        _para[0].Value = DetailID;
        _para[1] = new SqlParameter("@price", SqlDbType.Int);
        _para[1].Value = price;

        _dabasic.ExecuteReturnParameters(_comman, _para, CommandType.StoredProcedure);
    }

    //public void ChangeTicket()
    //{ }

    //public void CancelTicket()
    //{ }

    //public DataSet getTicketList()
    //{ }

    public DataSet getBookedTicketbyDate(int days)
    {
        int count = 0;

        string _command = "_Ticket_GetBookedTicketbyTime";

        SqlParameter[] _para = new SqlParameter[2];
        _para[0] = new SqlParameter("@days", SqlDbType.Int);
        _para[0].Value = days;
        _para[1] = new SqlParameter("@count", SqlDbType.Int);
        _para[1].Direction = ParameterDirection.Output;

        SqlParameterCollection paraCollect;
        DataSet ds;
        List<object> output = _dabasic.ExecuteReturnBoth(_command, _para, CommandType.StoredProcedure);

        paraCollect = output[0] as SqlParameterCollection;
        ds = output[1] as DataSet;

        count = Convert.ToInt32(paraCollect["@count"].Value);
        if (count > 0)
        {
            return ds;
        }
        else
        {
            return null;
        }

    }

    public void ExpiredTicket(int ID)
    {
        string _comman = "_Ticket_Expired";

        SqlParameter[] _para = new SqlParameter[1];

        _para[0] = new SqlParameter("@TicketID", SqlDbType.Int);
        _para[0].Value = ID;

        _dabasic.ExecuteReturnParameters(_comman, _para, CommandType.StoredProcedure);

    }

    public DataSet getTicketByID(int ID)
    {
        string _command = "_Ticket_GetbyID";

        SqlParameter[] _para = new SqlParameter[1];
        _para[0] = new SqlParameter("@ticketID", SqlDbType.Int);
        _para[0].Value = ID;

        return _dabasic.ExecuteGetDataSet(_command, _para, CommandType.StoredProcedure);
    }

    public DataSet getTicketDetail(int ticketid)
    {
        string _command = "_Ticket_GetDetails";

        SqlParameter[] _para = new SqlParameter[1];
        _para[0] = new SqlParameter("@ticketID", SqlDbType.Int);
        _para[0].Value = ticketid;

        return _dabasic.ExecuteGetDataSet(_command, _para, CommandType.StoredProcedure);
    }

}
